insert overwrite table jms_dm.dm_waybill_prescription_reach_details_dt_tmp1 partition(dt)
select  master.scantime taking_date  -- 揽收日期
       ,case when oms_order.order_source_code='D56' and (statistics.order_source not in ('1085','1133') or statistics.way_bill_no is null) then 1
             when oms_order.order_source_code='D02' and (statistics.order_source not in ('1085','1133') or statistics.way_bill_no is null) then 2
             when oms_order.order_source_code in ('D60','D09') and (statistics.order_source not in ('1085','1133') or statistics.way_bill_no is null) then 3
             when statistics.order_source='1085' then 4
             when statistics.order_source='1133' then 5
             else 6 end island  --订单来源 1oppo电商 2VIP系统 3桃花岛 4抖音 5快手 6其它
       ,master.waybill_no  -- 运单号
       ,waybill.customer_code  -- 客户编号
       ,waybill.customer_name  -- 客户名称
       ,start_network.subordinate_agent_code as start_subordinate_agent_code  -- 始发代理区code
       ,start_network.subordinate_agent_name as start_subordinate_agent_name  -- 始发代理区name
       ,start_network.parent_network_code    as start_parent_network_code  -- 始发加盟商code
       ,start_network.parent_network_name    as start_parent_network_name  -- 始发加盟商name
       ,start_sys_network.regional_id            as start_regional_id  -- 始发大区id
       ,start_sys_network.regional_desc          as start_regional_desc  -- 始发大区name
       ,start_network.provider_id            as start_provider_id  -- 始发省份id
       ,start_network.provider_desc          as start_provider_desc  -- 始发省份name
       ,start_network.city_id                as start_city_id  -- 始发城市id
       ,start_network.city_desc              as start_city_desc  -- 始发城市name
       ,case when start_network.city_id='441900' then '441900'
             when start_network.city_id='442000' then '442000'
               else start_network.area_id end as start_area_id  -- 始发区id
       ,case when start_network.city_id='441900' then '东莞市'
             when start_network.city_id='442000' then '中山市'
               else start_network.area_desc end as start_area_desc  -- 始发区name
       -- ,start_network.area_id                as start_area_id  -- 始发区id
       -- ,start_network.area_desc              as start_area_desc  -- 始发区name
       ,master.scansitecode            as start_pick_network_code  -- 始发网点code
       ,start_network.name            as start_pick_network_name  -- 始发网点name

       ,aim_network.subordinate_agent_code   as aim_subordinate_agent_code  -- 目的代理区code
       ,aim_network.subordinate_agent_name   as aim_subordinate_agent_name  -- 目的代理区name
       ,aim_network.parent_network_code      as aim_parent_network_code  -- 目的加盟商code
       ,aim_network.parent_network_name      as aim_parent_network_name  -- 目的加盟商name
       ,aim_sys_network.regional_id              as aim_regional_id  -- 目的大区id
       ,aim_sys_network.regional_desc            as aim_regional_desc  -- 目的大区name
       ,aim_network.provider_id              as aim_provider_id  -- 目的省份id
       ,aim_network.provider_desc            as aim_provider_desc  -- 目的省份name
       ,aim_network.city_id                  as aim_city_id  -- 目的城市id
       ,aim_network.city_desc                as aim_city_desc  -- 目的城市name
       ,case when aim_network.city_id='441900' then '441900'
             when aim_network.city_id='442000' then '442000'
                else aim_network.area_id end as aim_area_id  -- 目的区id
       ,case when aim_network.city_id='441900' then '东莞市'
             when aim_network.city_id='442000' then '中山市'
                else aim_network.area_desc end as aim_area_desc  -- 目的区name
       -- ,aim_network.area_id                  as aim_area_id  -- 目的区id
       -- ,aim_network.area_desc                as aim_area_desc  -- 目的区name
       ,waybill.dispatch_network_code        as aim_destination_code  -- 目的网点code
       ,waybill.dispatch_network_name        as aim_destination_name  -- 目的网点name

       ,taking.scantime  as taking_time  -- 揽收时间
       ,warehous.scantime  as warehous_time  -- 入仓时间
       ,sitesend.scantime as send_time  -- 发件扫描时间
       ,start_centerarrival.scansitecode as start_center_code  -- 始发转运中心code
       ,centerarrival_network.name as start_center_name -- 始发转运中心name
       ,start_centerarrival.scantime as start_centerarriva_time  -- 始发中心到件扫描时间
       ,start_unloading.transfercode as arrival_task_no  -- 始发中心到车任务号
       ,'到件' as type_one  -- 类型1
       ,start_centersend.scantime as start_centersend_time  -- 始发中心发件扫描时间
       ,start_centersend.transfercode as departure_task  -- 始发发车任务单号
       ,centersend_shipment.actual_departure_time as start_centersend_departure_time -- 始发中心发车时间
       ,case when waybill.is_sign=1 then aim_centerarrival.scansitecode
             else first_code.center_code  end as aim_center_code  -- 目的转运中心code
       ,case when waybill.is_sign=1 then aim_centerarrival.inputsite
             else first_code.center_name  end as aim_center_name  -- 目的转运中心name
       ,aim_centerarrival.scantime as aim_centerarriva_time  -- 目的中心到件扫描时间
       ,aim_unloading.transfercode as arrival_task  -- 目的中心到车任务单号
       ,centerarrival_shipment.actual_arrival_time as aim_centerarrival_departure_time -- 目的中心到车时间
       ,'到件' as type_two  -- 类型2
       ,aim_centersend.scantime as aim_centersend_time  -- 目的中心发件扫描时间

       ,case when waybill.sign_network_code is not null then waybill.sign_network_code
        when waybill.sign_network_code is null and waybill.dispatch_network_code is not null then waybill.dispatch_network_code
        when waybill.sign_network_code is null and waybill.dispatch_network_code is null then order_network.code
        else null end sign_network_code -- 签收网点code
        ,case when waybill.sign_network_code is not null then waybill.sign_network_name
        when waybill.sign_network_code is null and waybill.dispatch_network_code is not null then waybill.dispatch_network_name
        when waybill.sign_network_code is null and waybill.dispatch_network_code is null then order_network.name
        else null end sign_network_name -- 签收网点name

       -- ,case when siging.delivertime is not null then siging.scansitecode
             -- else second_code.network_code end as sign_network_code  -- 签收网点code
       -- ,case when siging.delivertime is not null then siging.inputsite
             -- else second_code.network_name  end as sign_network_name  -- 签收网点name
       ,sign_network.subordinate_agent_code   as sign_subordinate_agent_code  -- 签收代理区code
       ,sign_network.subordinate_agent_name   as sign_subordinate_agent_name  -- 签收代理区name
       ,sign_network.parent_network_code      as sign_parent_network_code  -- 签收加盟商code
       ,sign_network.parent_network_name      as sign_parent_network_name  -- 签收加盟商name
       ,sign_sys_network.regional_id              as sign_regional_id  -- 签收大区id
       ,sign_sys_network.regional_desc            as sign_regional_desc  -- 签收大区name
       ,sign_network.provider_id              as sign_provider_id  -- 签收省份id
       ,sign_network.provider_desc            as sign_provider_desc  -- 签收省份name
       ,sign_network.city_id                  as sign_city_id  -- 签收城市id
       ,sign_network.city_desc                as sign_city_desc  -- 签收城市name
       ,case when sign_network.city_id='441900' then '441900'
             when sign_network.city_id='442000' then '442000'
                else sign_network.area_id end as sign_area_id  -- 签收区id
       ,case when sign_network.city_id='441900' then '东莞市'
             when sign_network.city_id='442000' then '中山市'
                else sign_network.area_desc end as sign_area_desc  -- 签收区name
       -- ,sign_network.area_id                  as sign_area_id  -- 签收区id
       -- ,sign_network.area_desc                as sign_area_desc  -- 签收区name
       ,siging.delivertime as pres_sign_time -- 时效签收时间
       ,sign.delivertime as real_sign_time  -- 实际签收时间
       ,from_unixtime(unix_timestamp(master.scantime),'yyyy-MM-dd') as dt
  from (
     select waybill_no,
            scantime,
            scansitecode
       from
  (
    select billcode as waybill_no,
           scantime,
           scansitecode
  from jms_dws.dws_actual_taking_dt
 where dt between '{{ execution_date | date_add(-6) | cst_ds }}' and '{{ execution_date |  cst_ds }}'
     ) t
  join jms_dim.dim_lmdm_sys_network_expand start_network on t.scansitecode = start_network.code
  ) master

  left join (
            select waybill_no  -- 运单号
                   ,customer_code  -- 客户编号
                   ,customer_name  -- 客户名称
                   ,pick_network_code  -- 始发网点code
                   ,pick_network_name  -- 始发网点name
                   ,dispatch_network_code  -- 目的网点code
                   ,dispatch_network_name  -- 目的网点name
                   ,is_sign
                   ,sign_network_code
                   ,sign_network_name
                   ,first_code as terminal_dispatch_first_code
                   ,concat(first_code,',',second_code) as terminal_dispatch_second_code
              from jms_dwd.dwd_yl_oms_oms_waybill_dt
         where dt between date_format(date_add('{{ execution_date | date_add(-6) | cst_ds }}' ,-7), 'yyyy-MM-dd') and '{{ execution_date |  cst_ds }}' ) waybill on master.waybill_no=waybill.waybill_no

  left join (select waybill_id,order_source_code,dispatch_network_code from jms_dwd.dwd_yl_oms_oms_order_dt where dt between '{{ execution_date | date_add(-6) | cst_ds }}' and '{{ execution_date |  cst_ds }}' ) oms_order on master.waybill_no=oms_order.waybill_id

  left join jms_dim.dim_lmdm_sys_network order_network on oms_order.dispatch_network_code=order_network.code
  left join jms_dim.dim_lmdm_sys_network_expand start_network on master.scansitecode = start_network.code
  left join jms_dim.dim_lmdm_sys_network_expand aim_network on waybill.dispatch_network_code = aim_network.code

  left join (-- 揽收时间
         select billcode,scansitecode_early as scansitecode,scantime_early as scantime
           from jms_dwd.dwd_barscan_taking_dt where dt between '{{ execution_date | date_add(-6) | cst_ds }}' and '{{ execution_date |  cst_ds }}' and dimtype = 'billcode'
  ) taking on master.waybill_no=taking.billcode
  left join (-- 入仓时间
         select billcode,scansitecode_early as scansitecode,scantime_early as scantime
           from jms_dwd.dwd_barscan_warehousing_dt where dt between '{{ execution_date | date_add(-6) | cst_ds }}' and '{{ execution_date |  cst_ds }}' and dimtype = 'billcode'
  )warehous on master.waybill_no=warehous.billcode

  left join (-- 发件时间
         select * from(
         select billcode,scansitecode,scantime,row_number() over (partition by billcode order by scantime asc) bill_rank
           from jms_ods.tab_barscan_sitesend where dt between '{{ execution_date | date_add(-6) | cst_ds }}' and '{{ execution_date |  cst_ds }}'
  )t where t.bill_rank=1) sitesend on master.waybill_no=sitesend.billcode
  left join (-- 取始发中心
         select * from(
         select billcode,scansitecode,scantime,transfercode,row_number() over (partition by billcode order by scantime asc) bill_rank
           from jms_ods.tab_barscan_centerarrival where dt between '{{ execution_date | date_add(-6) | cst_ds }}' and '{{ execution_date |  cst_ds }}'
  )t where t.bill_rank=1) start_centerarrival on master.waybill_no=start_centerarrival.billcode

  left join jms_dim.dim_lmdm_sys_network_expand centerarrival_network on start_centerarrival.scansitecode = centerarrival_network.code

  left join (--取始发中心卸车任务单号
      select * from(
         select billcode,scansitecode,scantime,transfercode,row_number() over (partition by billcode order by scantime asc) bill_rank
           from jms_ods.tab_barscan_unloading where dt between '{{ execution_date | date_add(-6) | cst_ds }}' and '{{ execution_date |  cst_ds }}'
  )t where t.bill_rank=1) start_unloading on master.waybill_no=start_unloading.billcode and start_centerarrival.scansitecode=start_unloading.scansitecode
  left join (-- 始发中心发件扫描时间
         select * from(
         select billcode,scansitecode,scantime,transfercode,row_number() over (partition by billcode order by scantime asc) bill_rank
           from jms_ods.tab_barscan_centersend where dt between '{{ execution_date | date_add(-6) | cst_ds }}' and '{{ execution_date |  cst_ds }}'
  )t where t.bill_rank=1) start_centersend on master.waybill_no=start_centersend.billcode and start_centerarrival.scansitecode=start_centersend.scansitecode

  left join (-- 根据任务单号取始发中心发车时间
         select * from(select shipment_no,end_network_code,actual_departure_time,row_number() over (partition by shipment_no,end_network_code order by actual_departure_time desc) bill_rank from jms_ods.yl_tms_branch_shipment where dt='{{ execution_date |  cst_ds }}' )t where bill_rank=1
         union all
         select * from(select shipment_no,end_code as end_network_code,actual_departure_time,row_number() over (partition by shipment_no,end_code order by actual_departure_time desc) bill_rank from jms_ods.yl_tmsnew_branch_shipment where dt='{{ execution_date |  cst_ds }}' )t where bill_rank=1) centersend_shipment on start_centersend.transfercode=centersend_shipment.shipment_no and start_centersend.scansitecode=centersend_shipment.end_network_code

  left join (-- 一段码取目的中心
         select distinct code,center_code ,center_name from jms_dim.dim_lmdm_sys_first_code where is_enable=1 and is_delete=1)  first_code on waybill.terminal_dispatch_first_code=first_code.code

  left join (-- 联合一段码根据签收状态取目的中心
         select * from(
         select billcode,scansitecode,inputsite,scantime,transfercode,row_number() over (partition by billcode order by scantime desc) bill_rank
           from jms_ods.tab_barscan_centerarrival where dt between '{{ execution_date | date_add(-6) | cst_ds }}' and '{{ execution_date |  cst_ds }}'
  )t where t.bill_rank=1 ) aim_centerarrival on master.waybill_no=aim_centerarrival.billcode

  left join (--取目的中心卸车任务单号
      select * from(
         select billcode,scansitecode,scantime,transfercode,row_number() over (partition by billcode order by scantime asc) bill_rank
           from jms_ods.tab_barscan_unloading where dt between '{{ execution_date | date_add(-6) | cst_ds }}' and '{{ execution_date |  cst_ds }}'
  )t where t.bill_rank=1) aim_unloading on master.waybill_no=aim_unloading.billcode and (case when waybill.is_sign=1 then aim_centerarrival.scansitecode else first_code.center_code end)=aim_unloading.scansitecode


  left join (-- 根据任务单号取目的中心到车时间
         select * from(select shipment_no,end_network_code,actual_arrival_time,row_number() over (partition by shipment_no,end_network_code order by actual_departure_time desc) bill_rank from jms_ods.yl_tms_branch_shipment where dt='{{ execution_date |  cst_ds }}' )t where bill_rank=1
         union all
         select * from(select shipment_no,end_code as end_network_code,actual_arrival_time,row_number() over (partition by shipment_no,end_code order by actual_departure_time desc) bill_rank from jms_ods.yl_tmsnew_branch_shipment where dt='{{ execution_date |  cst_ds }}' )t where bill_rank=1) centerarrival_shipment on aim_centerarrival.transfercode=centerarrival_shipment.shipment_no and aim_centerarrival.scansitecode=centerarrival_shipment.end_network_code

  left join (-- 目的中心发件扫描时间
         select * from(
         select billcode,scansitecode,scantime,row_number() over (partition by billcode order by scantime desc) bill_rank
           from jms_ods.tab_barscan_centersend where dt between '{{ execution_date | date_add(-6) | cst_ds }}' and '{{ execution_date |  cst_ds }}'
  )t where t.bill_rank=1) aim_centersend on master.waybill_no=aim_centersend.billcode and aim_centersend.scansitecode=(case when waybill.is_sign=1 then aim_centerarrival.scansitecode else first_code.center_code end)


  -- left join (--二段码取签收网点
      -- select  first_and_second_code,network_code,network_name
        -- from (
              -- select regexp_replace(concat(f.code,',',s.code),' ','') as first_and_second_code,
                     -- s.network_code,
                     -- s.network_name,
                     -- row_number() over (partition by concat(f.code,',',s.code) order by s.network_code desc) dis_rank
               -- from
                      -- (select distinct code,
                              -- center_code
                         -- from ods.yl_lmdm_sys_first_code
                        -- where is_enable=1 and is_delete=1) f
                         -- join
                      -- (select distinct code,
                              -- center_code,
                              -- network_code,
                              -- network_name,
                              -- id
                         -- from ods.yl_lmdm_sys_second_code
                        -- where is_enable=1
                          -- and is_delete=1 ) s
                          -- on f.center_code = s.center_code order  by first_and_second_code desc
                      -- ) t where dis_rank =1
                     -- ) second_code on waybill.terminal_dispatch_second_code=second_code.first_and_second_code

  left join ( -- 取时效签收时间
             select sg.billcode       as billcode,
                   sg.scansitecode   as scansitecode,
                     sg_network.name   as inputsite,
                     sg.delivertime    as delivertime
                   from
              (select billcode,scansitecode,scantime as delivertime from jms_dws.dws_actual_aging_sign_dt
                 where dt between '{{ execution_date | date_add(-6) | cst_ds }}' and '{{ execution_date |  cst_ds }}'
                 and scantype='prior_aging_sign' ) sg
              left join jms_dim.dim_lmdm_sys_network_expand sg_network on sg.scansitecode = sg_network.code
              ) siging on siging.billcode=master.waybill_no
  left join jms_dim.dim_lmdm_sys_network_expand sign_network on sign_network.code = (case when siging.delivertime is not null then siging.scansitecode
  else (case when waybill.sign_network_code is not null then waybill.sign_network_code
        when waybill.sign_network_code is null and waybill.dispatch_network_code is not null then waybill.dispatch_network_code
        when waybill.sign_network_code is null and waybill.dispatch_network_code is null then order_network.code
        else null end) end)

  left join ( -- 取实际签收时间
                      select billcode,scantime as delivertime from jms_dws.dws_actual_aging_sign_dt
                 where dt between '{{ execution_date | date_add(-6) | cst_ds }}' and '{{ execution_date |  cst_ds }}'
                 and scantype='prior_actual_sign'
            ) sign on sign.billcode=master.waybill_no

  left join jms_dim.dim_lmdm_sys_network  start_sys_network on start_network.subordinate_agent_code=start_sys_network.code
  left join jms_dim.dim_lmdm_sys_network  aim_sys_network on aim_network.subordinate_agent_code=aim_sys_network.code
  left join jms_dim.dim_lmdm_sys_network  sign_sys_network on sign_network.subordinate_agent_code=sign_sys_network.code
  left join (select * from(select order_source,way_bill_no,row_number() over (partition by way_bill_no order by create_time desc) bill_rank from jms_ods.tab_statistics where dt>='{{ execution_date | date_add(-13) | cst_ds }}' and dt<='{{ execution_date |  cst_ds }}')t where t.bill_rank=1) statistics on master.waybill_no=statistics.way_bill_no ;
